package com.yaodian100.ec.product.dao.impl;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;

import com.yaodian100.core.admin.entity.User;
import com.yaodian100.core.admin.entity.UserPermission;
import com.yaodian100.core.admin.entity.Permission.Type;
import com.yaodian100.core.admin.service.UserService;
import com.yaodian100.core.admin.utils.AdminHelper;
import com.yaodian100.core.common.dao.impl.HibernateObjectDaoImpl;
import com.yaodian100.core.common.exception.CoreException;
import com.yaodian100.ec.product.dao.AddOnProductDao;
import com.yaodian100.ec.product.entity.AddOnProduct;

public class AddOnProductDaoImpl extends HibernateObjectDaoImpl<AddOnProduct> implements AddOnProductDao {
	@Autowired
	private UserService userService;
	private String pmGroupCode = "A-20";

	public Number getAddOnProductListSize(String mainProductId, String addOnProductId, String status)
			throws CoreException {
		StringBuffer sql = new StringBuffer();
		// 分線維護權限檢核

		sql.append("select count(*) from ( ");
		sql.append("SELECT x2.addon_id, x2.main_product_id, x1.product_name, x2.addon_product_id,");
		sql.append("(SELECT distinct a.product_name ");
		sql.append("FROM ec_product_main a, ec_addon_product b,ec_category_main c ");
		sql.append("WHERE b.addon_product_id =  a.product_id and c.category_id = a.main_category_id ");
		sql.append("AND x2.addon_product_id =a.product_id and a.product_type not in ('1','6') ");
		sql.append("AND b.is_delete =  'N') addon_product_name, ");
		sql
				.append("x2.addon_price, x2.start_date, x2.end_date, x2.is_enable,x2.addon_desc,x2.apply_member_rank,x2.is_delete ");
		sql.append("FROM ");
		sql.append("ec_product_main x1, ");
		sql.append("ec_addon_product x2, ");
		sql.append("ec_category_main x3 ");
		sql.append("WHERE ");
		sql.append("x3.category_id = x1.main_category_id ");
		sql.append("and x2.main_product_id =  x1.product_id ");
		sql.append("and x1.product_type not in ('1','6') AND x2.is_delete =  'N' ");
		if (StringUtils.isNotBlank(mainProductId)) {
			sql.append("and x2.main_product_id ='").append(mainProductId).append("' ");
		}
		if (StringUtils.isNotBlank(addOnProductId)) {
			sql.append("and x2.addon_product_id ='").append(addOnProductId).append("' ");
		}
		if (StringUtils.isNotBlank(status) && status.equals("1")) {// 未開始
			sql.append("and x2.start_date > current_timestamp ");
		}
		if (StringUtils.isNotBlank(status) && status.equals("2")) {// 進行中
			sql.append("and x2.start_date <= current_timestamp ");
			sql.append("and x2.end_date >= current_timestamp ");
		}
		if (StringUtils.isNotBlank(status) && status.equals("3")) {// 已結束
			sql.append("and x2.end_date < current_timestamp ");
		}

		User user = AdminHelper.getUser();
		if (null != user) {
			// 若 user 是 PM部門, 檢核 product.category 的 orgnizationPath 是否合乎規定
			int permissionSize = user.getPermissions(Type.CATEGORY).size();
			if (permissionSize > 0) {
				int i = 0;
				sql.append(" and ( ");
				for (UserPermission up : user.getPermissions(Type.CATEGORY).values()) {
					sql.append(" x3.category_path like '").append(up.getPermissionCode()).append("/%' ");
					if (i != permissionSize - 1) {
						sql.append(" or ");
					}
					i++;
				}
				sql.append(" ) ");
			}
		}
		sql.append(") q,ec_product_main pm,ec_category_main cm ");
		sql.append(" where pm.main_category_id = cm.category_id and q.addon_product_id = pm.product_id ");
		if (null != user) {
			// 若 user 是 PM部門, 檢核 product.category 的 orgnizationPath 是否合乎規定
			int permissionSize = user.getPermissions(Type.CATEGORY).size();
			if (permissionSize > 0) {
				int i = 0;
				sql.append(" and ( ");
				for (UserPermission up : user.getPermissions(Type.CATEGORY).values()) {
					sql.append(" cm.category_path like '").append(up.getPermissionCode()).append("/%' ");
					if (i != permissionSize - 1) {
						sql.append(" or ");
					}
					i++;
				}
				sql.append(" ) ");
			}
		}
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		List lists = query.list();
		return NumberUtils.createNumber(lists.get(0).toString());
	}

	public List<String> getAddOnProductList(String mainProductId, String addOnProductId, String status, int firstRecord,
			int maxRecord) throws CoreException {
		StringBuffer sql = new StringBuffer();
		// 分線維護權限檢核
		sql.append("select q.* from ( ");
		sql.append("SELECT x2.addon_id, x2.main_product_id, x1.product_name, x2.addon_product_id,");
		sql.append("(SELECT distinct a.product_name ");
		sql.append("FROM ec_product_main a, ec_addon_product b,ec_category_main c ");
		sql.append("WHERE b.addon_product_id =  a.product_id and c.category_id = a.main_category_id ");
		sql.append("AND x2.addon_product_id =a.product_id and a.product_type not in ('1','6') ");
		sql.append("AND b.is_delete =  'N') addon_product_name, ");
		sql
				.append("x2.addon_price, x2.start_date, x2.end_date, x2.is_enable,x2.addon_desc,x2.apply_member_rank,x2.is_delete ");
		sql.append("FROM ");
		sql.append("ec_product_main x1, ");
		sql.append("ec_addon_product x2, ");
		sql.append("ec_category_main x3 ");
		sql.append("WHERE ");
		sql.append("x3.category_id = x1.main_category_id ");
		sql.append("and x2.main_product_id =  x1.product_id ");
		sql.append("and x1.product_type not in ('1','6') AND x2.is_delete =  'N' ");
		if (StringUtils.isNotBlank(mainProductId)) {
			sql.append("and x2.main_product_id ='").append(mainProductId).append("' ");
		}
		if (StringUtils.isNotBlank(addOnProductId)) {
			sql.append("and x2.addon_product_id ='").append(addOnProductId).append("' ");
		}
		if (StringUtils.isNotBlank(status) && status.equals("1")) {// 未開始
			sql.append("and x2.start_date > current_timestamp ");
		}
		if (StringUtils.isNotBlank(status) && status.equals("2")) {// 進行中
			sql.append("and x2.start_date <= current_timestamp ");
			sql.append("and x2.end_date >= current_timestamp ");
		}
		if (StringUtils.isNotBlank(status) && status.equals("3")) {// 已結束
			sql.append("and x2.end_date < current_timestamp ");
		}

		User user = AdminHelper.getUser();
		if (null != user) {
			// 若 user 是 PM部門, 檢核 product.category 的 orgnizationPath 是否合乎規定
			int permissionSize = user.getPermissions(Type.CATEGORY).size();
			if (permissionSize > 0) {
				int i = 0;
				sql.append(" and ( ");
				for (UserPermission up : user.getPermissions(Type.CATEGORY).values()) {
					sql.append(" x3.category_path like '").append(up.getPermissionCode()).append("/%' ");
					if (i != permissionSize - 1) {
						sql.append(" or ");
					}
					i++;
				}
				sql.append(" ) ");
			}
		}
		sql.append(") q,ec_product_main pm,ec_category_main cm ");
		sql.append(" where pm.main_category_id = cm.category_id and q.addon_product_id = pm.product_id ");
		if (null != user) {
			// 若 user 是 PM部門, 檢核 product.category 的 orgnizationPath 是否合乎規定
			int permissionSize = user.getPermissions(Type.CATEGORY).size();
			if (permissionSize > 0) {
				int i = 0;
				sql.append(" and ( ");
				for (UserPermission up : user.getPermissions(Type.CATEGORY).values()) {
					sql.append(" cm.category_path like '").append(up.getPermissionCode()).append("/%' ");
					if (i != permissionSize - 1) {
						sql.append(" or ");
					}
					i++;
				}
				sql.append(" ) ");
			}
		}
		SQLQuery query = getSession().createSQLQuery(sql.toString());
		if (firstRecord != -1 && maxRecord != -1) {
			query.setFirstResult(firstRecord);
			query.setMaxResults(maxRecord);
		}
		List lists = query.list();
		return lists;
	}
}